﻿using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Easy.Common;

namespace Easy.DAL
{
    public class EntityDAL
    {
        private readonly SqlDatabase sqldb = new SqlDatabase(Config.TulingBuyCrmConnectString);
        private string EntityName;

        public EntityDAL(string entityName)
        {
            string commandText = string.Format(@"select * from EntityMap where EntityName=@entityName");
            SqlParameter[] pams = new SqlParameter[] { new SqlParameter("@entityName", entityName) };
            DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
            cmd.Parameters.AddRange(pams);
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            if (dt.Rows.Count==0)
            {
                throw new Exception(string.Format(@"EntityName({0}) Error!", entityName));
            }
            this.EntityName = entityName; 
        }

        #region 实体结构相关

        //表单字段
        public DataTable GetAttributeMapData(string ViewName = null)
        { 
            if(string.IsNullOrWhiteSpace(ViewName))
            {
                string commandText = string.Format(@"--查询实体字段定义
                                                SELECT  Name ,
                                                        DisplayName ,
                                                        Type ,
                                                        Length ,
                                                        IsNullable ,
                                                        Cansearchable ,
                                                        Format ,
                                                        LookupEntityName ,
                                                        IsPrimaryAtt ,
                                                        IsGlobalPicklist ,
                                                        GlobalPicklist ,
                                                        LookupPrimaryAtt,
                                                        NULL as AttributeWidth,
                                                        NULL as ViewOrderBy
                                                FROM    dbo.AttributeMap WITH(NOLOCK)
                                                WHERE   EntityName = @entityName");
                SqlParameter[] pams = new SqlParameter[] { new SqlParameter("@entityName", this.EntityName) };
                DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
                cmd.Parameters.AddRange(pams);
                DataSet ds = sqldb.ExecuteDataSet(cmd);
                return ds.Tables[0];
            }
            else
            {
                string commandText = string.Format(@"--查询实体字段定义
                                          SELECT Name ,
                                                DisplayName ,
                                                Type ,
                                                Length ,
                                                IsNullable ,
                                                Cansearchable ,
                                                Format ,
                                                LookupEntityName ,
                                                IsPrimaryAtt ,
                                                IsGlobalPicklist ,
                                                GlobalPicklist ,
                                                LookupPrimaryAtt,
												vm.AttributeWidth,
                                                vm.ViewOrderBy
                                         FROM   dbo.AttributeMap am WITH ( NOLOCK )
                                                INNER JOIN ViewAttMap vm WITH ( NOLOCK ) ON vm.AttributeName = am.name                          
												inner join ViewMap m with(nolock) on m.ViewMapId=vm.ViewId  
																						AND m.EntityName = @entityName
                                                                                         AND m.ViewName = @viewName
                                         WHERE  am.EntityName = @entityName
                                         ORDER BY vm.AttributeOrder");
                SqlParameter[] pams = new SqlParameter[]{
	                new SqlParameter("@entityName",EntityName),
                    new SqlParameter("@viewName",ViewName)
                };
                DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
                cmd.Parameters.AddRange(pams);
                DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
                return dt;
            }
        }

        /// <summary>
        /// 获取视图中要显示的字段
        /// </summary>
        /// <param name="ViewName"></param>
        /// <returns></returns>
        public DataTable GetViewAttMapData(string ViewName)
        {
            string commandText = string.Format(@"--获取视图中要显示的字段
                                          select 
	                                        vam.AttributeName as Name,
                                            (case when am.DisplayName is null then vam.LinkAttributeName 
		                                        when vam.AttributeName<>vam.LinkAttributeName then (am.DisplayName+'('+emp.EntityDisplayName+')')
	                                         else am.DisplayName end) as DisplayName,
                                            vam.EntityName,
                                            isnull(am.[Type],'nvarchar') as [Type],
	                                        (case when (am.[Type] is null) then '200' else am.[Length] end) as [Length],
                                            isnull(am.IsNullable,1) as IsNullable,
                                            isnull(am.Cansearchable,1) as Cansearchable,
                                            am.[Format] ,
                                            am.LookupEntityName ,
                                            am.IsPrimaryAtt ,
                                            am.IsGlobalPicklist ,
                                            am.GlobalPicklist ,
                                            am.LookupPrimaryAtt,
	                                        vam.AttributeWidth,
                                            vam.ViewOrderBy
                                        from ViewMap as vm with(nolock)
                                        inner join ViewAttMap as vam with(nolock)
	                                        on vam.ViewId=vm.ViewMapId
                                        left join AttributeMap as am with(nolock)
	                                        on am.EntityName = isnull(vam.LinkEntityName, vam.EntityName)
	                                        and am.name=isnull(vam.LinkAttributeName, vam.AttributeName)
                                        left join EntityMap as emp with(nolock)
	                                        on emp.EntityName=am.EntityName 
                                        where vm.EntityName=@entityName  and vm.ViewName = @viewName
                                        ORDER BY vam.AttributeOrder");
            SqlParameter[] pams = new SqlParameter[]{
	                new SqlParameter("@entityName",EntityName),
                    new SqlParameter("@viewName",ViewName)
                };
            DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
            cmd.Parameters.AddRange(pams);
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            return dt;

        }

        /// <summary>
        /// 根据视图中配置的存储过程，获取字段
        /// </summary>
        /// <param name="ViewName"></param>
        /// <returns></returns>
        public DataTable GetAttributeMapBySP(string ViewName)
        {
            //查找存储过程
            string commandText = string.Format(@"SELECT top 1 AttProcedure FROM  ViewMap 
                                    where ViewName='{0}' and EntityName='{1}'", ViewName,EntityName);
            DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
            DataSet ds = sqldb.ExecuteDataSet(cmd);
            DataTable dt=ds.Tables[0];
            string strProcedureName ="";
            if(dt.Rows.Count==0)
            {
                strProcedureName = "SYS_ViewAttCommon";
            }
            else
            {
                strProcedureName = dt.Rows[0][0].ToString();
            }
           
            object[] param = { 
                         "1",
                         DBNull.Value,
                         "",
                         EntityName,
                         "",

                         "",
                         "",
                        DBNull.Value,
                        DBNull.Value,
                        "",

                        "",
                        "",
                        "",
                        ViewName
                     };


            DataSet ds1 = sqldb.ExecuteDataSet(strProcedureName, param);
            return ds1.Tables[0];
            
            
        }
        //PickList值
        public DataTable GetStringMapData(string ViewName = null)
        {
            string commandText = "";
            SqlParameter[] pams = new SqlParameter[] { };
            if (string.IsNullOrEmpty(ViewName))
            {
                commandText = string.Format(@"--获取实体所有选项集
                                                SELECT  AttributeName Name ,
                                                        AttributeValue Value,
                                                        Value Text,
                                                        DisplayOrder
                                                FROM    dbo.StringMap WITH ( NOLOCK )
                                                WHERE EntityName=@entityName                                        
                                                
                                                UNION ALL
                                                
                                                SELECT att.name Name,
                                                gsm.AttributeValue Value,
                                                gsm.value Text,
                                                gsm.DisplayOrder
                                                FROM dbo.AttributeMap att WITH(NOLOCK)
                                                INNER JOIN dbo.GlobalStringMap gsm WITH(NOLOCK)
                                                ON att.IsGlobalPicklist=1 AND att.GlobalPicklist=gsm.Name
                                                WHERE EntityName=@entityName
                                                ORDER BY Name,DisplayOrder");
                pams = new SqlParameter[]{
	                new SqlParameter("@entityName",EntityName)
                };
            }
            else
            {
                commandText = string.Format(@"SELECT 
	                                                vam.AttributeName Name ,
                                                    sm.AttributeValue Value ,
                                                    sm.Value Text ,
                                                    sm.DisplayOrder
                                                FROM ViewMap as vm with(nolock) 
                                                inner join ViewAttMap as vam WITH ( NOLOCK ) 
	                                                ON vam.ViewId=vm.ViewMapId 
                                                INNER JOIN dbo.StringMap sm WITH ( NOLOCK )
	                                                on sm.EntityName=isnull(vam.LinkEntityName,vam.EntityName)
	                                                and sm.AttributeName=isnull(vam.LinkAttributeName,vam.AttributeName)      
                                                WHERE vm.EntityName = @entityName and vm.ViewName = @viewName

                                                UNION ALL

                                                SELECT 
	                                                vam.AttributeName Name ,
                                                    gsm.AttributeValue Value ,
                                                    gsm.value Text ,
                                                    gsm.DisplayOrder
                                                FROM ViewMap as vm with(nolock) 
                                                inner join ViewAttMap vam WITH ( NOLOCK ) 
	                                                on vam.ViewId=vm.ViewMapId 
                                                inner join AttributeMap att WITH ( NOLOCK )
	                                                on att.EntityName=isnull(vam.LinkEntityName,vam.EntityName)
	                                                and att.name=isnull(vam.LinkAttributeName,vam.AttributeName)
	                                                and att.IsGlobalPicklist = 1 
                                                inner join dbo.GlobalStringMap gsm WITH ( NOLOCK ) 
	                                                on att.GlobalPicklist = gsm.Name 
                                                WHERE vm.EntityName = @entityName and vm.ViewName = @viewName
                                                ORDER BY Name , DisplayOrder
                                                ");

                pams = new SqlParameter[]{
	                new SqlParameter("@entityName",EntityName),
                    new SqlParameter("@viewName",ViewName)
                };
            }
            DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
            cmd.Parameters.AddRange(pams);
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            return dt;
        }

        //视图 
        public DataTable GetViewMapData()
        {
            string commandText = string.Format(@"--查找视图
                                                SELECT  ViewName ,
                                                        ViewDisplayName,
                                                        ViewAdditionalJs,
                                                        isnull(ClientType,10) as ClientType
                                                FROM    ViewMap
                                                WHERE   EntityName = @entityName
                                                    and (IsDisplay is null or IsDisplay=1) 
                                                ORDER BY  CreatedOn ");
            SqlParameter[] pams = new SqlParameter[]{
	            new SqlParameter("@entityName",this.EntityName)
            };
            DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
            cmd.Parameters.AddRange(pams);
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            return dt;
        }

        //视图上的条件
        public string GetViewFilter(string EntityName, string ViewName)
        {
            string where = "";

            string commandText = string.Format(@"--查找视图筛选条件
                                                SELECT top 1 isnull(ViewFilter,'') as ViewFilter
                                                FROM    ViewMap
                                                WHERE   EntityName = @entityName
                                                        AND ViewName = @viewName ");
            SqlParameter[] pams = new SqlParameter[]{ 
	            new SqlParameter("@entityName",EntityName),
                new SqlParameter("@viewName",ViewName)
            };
            DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
            cmd.Parameters.AddRange(pams);
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            if(dt.Rows.Count>0)
            {
                where = dt.Rows[0][0].ToString();
            }
            return where;
        }

        #endregion

        #region 实体数据相关

        public void Create(string colNames,string values)
        {
            string commandText = string.Format(@"INSERT INTO {0} ({1}) VALUES ({2})", this.EntityName, colNames, values);
            sqldb.ExecuteNonQuery(CommandType.Text, commandText);
        }

        public void Update(string sql)
        {
            sqldb.ExecuteNonQuery(CommandType.Text, sql);
        }

        public DataTable Retrieve(string sql)
        {
            DataSet ds = sqldb.ExecuteDataSet(CommandType.Text, sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            return null;
        }

        //查询多条记录
        public DataSet RetrieveMultiple(string select, string order, string selectAll, string EntityName, string join, string wherePrivilege, string where, int startRow, int endRow)
        {
            string commandText = string.Format(@"SELECT {0} FROM 
                                                (
                                                    SELECT ROW_NUMBER() OVER ( ORDER BY t.{1} ) AS rowId ,t.*
                                                    FROM ( 
                                                           SELECT {2}
                                                           FROM {3} base WITH(NOLOCK) 
                                                           {4}  --关联lookup实体
                                                           {5}  --where权限
                                                         ) AS t
													{6}  --where条件
											    ) AS tt
                                               WHERE tt.rowId BETWEEN {7} AND {8} 

                                                select COUNT(1) AS total
                                                FROM ( 
                                                        SELECT {2}
                                                        FROM {3} base WITH(NOLOCK) 
                                                        {4}  --关联lookup实体
                                                        {5}  --where权限
                                                        ) AS t
												{6}  --where条件
                                            ", select, order, selectAll, EntityName, join, wherePrivilege, where, startRow, endRow);

            DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
            DataSet ds = sqldb.ExecuteDataSet(cmd);
            return ds;
        }

        //GetReportListData.ashx
        public DataSet getReportListNew(string entityname, string procname, string param1, string param2) {
            string strProcedureName = procname;
            object[] param = new object[] { entityname};
            //DataSet ds = sqldb.ExecuteDataSet(strProcedureName, param);
            DataSet ds = sqldb.ExecuteDataSet(strProcedureName,param);
            return ds;
        }
        //end



        //modify:kyzhao 查询多条记录
        public DataSet RetrieveMultipleNew(string viewName,string where, string entity,  int startRow, int endRow,string order,Guid userId)
        {
            //查找存储过程
            string commandText = string.Format(@"SELECT top 1 AttProcedure FROM  ViewMap 
                                        where ViewName='{0}' and EntityName='{1}'",viewName, entity );
            DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
            DataSet ds = sqldb.ExecuteDataSet(cmd);
            string strProcedureName = "";
            if (ds.Tables[0].Rows.Count == 0)
            {
                strProcedureName = "SYS_ViewAttCommon";
            }
            else
            {
                strProcedureName = ds.Tables[0].Rows[0][0].ToString();
            }
            
            object[] param1 = new object[] {
               "2",
               userId.ToString(),
               where,
               entity,
               startRow,

               endRow,
               order,
               DBNull.Value,
               DBNull.Value,
               "",

               "",
               "",
               "",
               viewName
            };
            DataSet ds1 = sqldb.ExecuteDataSet(strProcedureName, param1);
            return ds1;
            
        }
        //create:kyzhao
        public DataSet RetrieveMultipleMobile(string viewName, string where, string entity, string order, Guid userId, string lastordervalue, string lastid, string ordershort,string pagesize)
        {
            //查找存储过程
            string commandText = string.Format(@"SELECT top 1 AttProcedure FROM  ViewMap 
                                        where ViewName='{0}' and EntityName='{1}'", viewName, entity);
            DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
            DataSet ds = sqldb.ExecuteDataSet(cmd);
            string procedurename = ds.Tables[0].Rows[0][0].ToString();
            
            object[] param1 = new object[] {
               "3",
               userId.ToString(),
               where,
               entity,
               "",
               "",
               order,

               DBNull.Value,
               DBNull.Value,
               lastordervalue,
               lastid == "00000000-0000-0000-0000-000000000000" ? null: lastid,
               ordershort,
               pagesize,
               viewName
            };
            DataSet ds1 = sqldb.ExecuteDataSet(procedurename, param1);
            return ds1;
           
        }

        public DataTable RetrievePreviousOrNextPage(int pageSize, string select, string selectAll, string EntityName, string join, string wherePrivilege, string where, string order, string lastOrderValue, Guid lastId)
        {
            string commandText = string.Format(@"SELECT top {0} {1}
                                                    FROM ( 
                                                           SELECT {2}
                                                           FROM {3} base WITH(NOLOCK) 
                                                           {4}  --关联lookup实体
                                                           {5}  --where权限
                                                         ) AS t
													{6}  --where条件
                                                    {7} --order by"
                , pageSize, select, selectAll, EntityName, join, wherePrivilege, where, order);
            DbCommand cmd = sqldb.GetSqlStringCommand(commandText);
            if (lastId != Guid.Empty)
            {
                cmd.Parameters.Add(new SqlParameter("@lastId", lastId));
                if (lastOrderValue != null)
                {
                    cmd.Parameters.Add(new SqlParameter("@lastOrderValue", lastOrderValue));
                }
            }
            DataSet ds = sqldb.ExecuteDataSet(cmd);
            return ds.Tables[0];
        }
        
        public void Assign(Guid ID, Guid OwingUser)
        {
            String UpdateSqlFormat = "UPDATE {0} SET OwningUser=@owninguser , OwningBusinessUnit=(select max(convert(nvarchar(50),BusinessUnitId)) from SystemUser as S where S.SystemUserId=@owninguser) WHERE {0}Id=@ID";
            String UpdateSql = String.Format(UpdateSqlFormat, EntityName);
            DbCommand UpdateCommand = sqldb.GetSqlStringCommand(UpdateSql);
            UpdateCommand.Parameters.Add(new SqlParameter("@Id", ID));
            UpdateCommand.Parameters.Add(new SqlParameter("@owninguser", OwingUser));


            sqldb.ExecuteNonQuery(UpdateCommand);
        }

        #endregion
    }
}
